package edu.gatech.gro.model.dao;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import edu.gatech.gro.model.Item;
import edu.gatech.gro.model.ListGroup;
import edu.gatech.gro.model.ListItem;
import edu.gatech.gro.model.NamedList;

public class NamedListDao extends DatabaseHandler {

	private static final String TAG = "NAMED_LIST_DAO";

	public NamedListDao(Context context) {
		super(context);
	}

	@Override
	public void clear() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(NAMED_LIST_TABLE, "id > 0", null);
		db.close();
	}

	public ArrayList<NamedList> getAllNamedLists(final int userId) {
		ArrayList<NamedList> list = new ArrayList<NamedList>();
		String selectQuery = "SELECT  * FROM " + NAMED_LIST_TABLE + " WHERE userId = ?";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, new String[] { String.valueOf(userId) });

		if (cursor.moveToFirst()) {
			do {
				NamedList namedList = new NamedList();
				namedList.setId(cursor.getInt(0));
				namedList.setUserId(cursor.getInt(1));
				namedList.setName(cursor.getString(2));
				namedList.setNameClean(cursor.getString(3));
				namedList.setCreationTime(cursor.getInt(4));
				namedList.setLastUpdateTime(cursor.getInt(5));
				namedList.setDeleteFlag(cursor.getInt(6) == 1);
				list.add(namedList);
			} while (cursor.moveToNext());
		}
		db.close();

		return list;
	}

	public NamedList getNamedListByNameClean(final int userId, final String nameClean) {
		NamedList list = null;
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT  * FROM " + NAMED_LIST_TABLE + " WHERE userId = ? AND nameClean = ?";

		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(userId), nameClean });
		if (cursor != null) {
			cursor.moveToFirst();

			list = new NamedList();
			list.setId(cursor.getInt(0));
			list.setUserId(cursor.getInt(1));
			list.setName(cursor.getString(2));
			list.setNameClean(cursor.getString(3));
			list.setCreationTime(cursor.getInt(4));
			list.setLastUpdateTime(cursor.getInt(5));
			list.setDeleteFlag(cursor.getInt(6) == 1);
		}
		cursor.close();
		db.close();
		return list;
	}

	public ArrayList<ListGroup> getAllItems(final int listId) {
		ArrayList<ListGroup> arr = new ArrayList<ListGroup>();

		String sql = "SELECT Item.*, ItemType.name AS typeName, NamedListItem.checked, NamedListItem.quantity, NamedListItem.currentQuantity FROM Item JOIN NamedListItem ON NamedListItem.itemId = Item.id JOIN ItemType ON ItemType.id = Item.itemTypeId WHERE NamedListItem.namedListId = ? ORDER BY ItemType.name ASC, Item.name ASC";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(listId) });

		int previousCategory = 0;
		ArrayList<Item> items = null;
		int lastTypeId = 0;
		String lastTypeName = "";

		if (cursor.moveToFirst()) {
			int colItemTypeId = cursor.getColumnIndex("itemTypeId");
			int colTypeName = cursor.getColumnIndex("typeName");
			int colChecked = cursor.getColumnIndex("checked");
			int colQuantity = cursor.getColumnIndex("quantity");
			int colCurrentQuantity = cursor.getColumnIndex("currentQuantity");
			do {
				if (cursor.getInt(colItemTypeId) != previousCategory) {
					if (items != null) {
						ListGroup group = new ListGroup();
						group.setId(lastTypeId);
						group.setName(lastTypeName);
						group.setItems(items);
						arr.add(group);
					}
					previousCategory = cursor.getInt(colItemTypeId);
					items = new ArrayList<Item>();
				}
				ListItem item = new ListItem();
				item.setId(cursor.getInt(0));
				item.setItemTypeId(cursor.getInt(1));
				item.setGroceryStoreId(cursor.getInt(2));
				item.setName(cursor.getString(3));
				item.setNameClean(cursor.getString(4));
				item.setBarCode(cursor.getString(5));
				item.setBarCodeHash(cursor.getString(6));
				item.setPrice(cursor.getDouble(7));
				item.setCurrency(cursor.getInt(8));
				item.setCreationTime(cursor.getInt(9));
				item.setLastUpdateTime(cursor.getInt(10));
				item.setDeleteFlag(cursor.getInt(11) == 1);
				item.setChecked(cursor.getInt(colChecked) == 1);
				item.setQuantity(cursor.getInt(colQuantity));
				item.setCurrentQuantity(cursor.getInt(colCurrentQuantity));
				item.setNamedListId(listId);
				items.add(item);

				lastTypeId = cursor.getInt(colItemTypeId);
				lastTypeName = cursor.getString(colTypeName);
			} while (cursor.moveToNext());
		}
		if (items != null) {
			ListGroup group = new ListGroup();
			group.setId(lastTypeId);
			group.setName(lastTypeName);
			group.setItems(items);
			arr.add(group);
		}
		db.close();

		return arr;
	}

	public int count() {
		String countQuery = "SELECT  * FROM " + NAMED_LIST_TABLE;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();
		return cursor.getCount();
	}

	public boolean saveNamedList(NamedList namedList) {
		if (namedList == null) {
			return false;
		}

		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("id", namedList.getId());
		values.put("userId", namedList.getUserId());
		values.put("name", namedList.getName());
		values.put("nameClean", namedList.getNameClean());
		values.put("creationTime", namedList.getCreationTime());
		values.put("lastUpdateTime", namedList.getLastUpdateTime());
		values.put("deleteFlag", namedList.isDeleteFlag() ? 1 : 0);

		long id = db.insert(NAMED_LIST_TABLE, null, values);
		if (id == -1) {
			// TODO: error
			return false;
		} else {
			namedList.setId((int) id);
		}

		db.close();
		return true;
	}

	public boolean updateNamedList(NamedList namedList) {
		if (namedList == null || namedList.getId() < 1) {
			return false;
		}
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("name", namedList.getName());
		values.put("nameClean", namedList.getNameClean());
		values.put("lastUpdateTime", namedList.getLastUpdateTime());
		values.put("deleteFlag", namedList.isDeleteFlag() ? 1 : 0);

		// updating row
		int r = db.update(NAMED_LIST_TABLE, values, "id = ?", new String[] { String.valueOf(namedList.getId()) });
		db.close();
		return r == 1;
	}

	public boolean deleteNamedList(NamedList namedList) {
		if (namedList == null || namedList.getId() < 1) {
			return false;
		}
		SQLiteDatabase db = this.getWritableDatabase();
		int r = db.delete(NAMED_LIST_TABLE, "id = ?", new String[] { String.valueOf(namedList.getId()) });
		db.close();
		return r == 1;
	}
}
